/**
 * 销售单相关功能
 */
import { mysql } from 'src-ssr/db/connect';
import TableHelper from 'src-ssr/db/tableHelper';
import * as tools from 'src-ssr/util/common';
import logs from 'src-ssr/util/logs';
import { Sequelize, Op, QueryTypes, where } from 'sequelize';
import models, { sequelize } from 'src-ssr/db/sequelize';
import moment from 'moment';
import autocode from 'src-ssr/util/autocode';
import shortid from 'shortid';
import { URL } from 'node:url';
import workflow from 'src-ssr/util/workflow';
import * as exportFile from 'src-ssr/util/exportToFile';
import path from 'path';
import fs from 'fs';

import * as singleUtils from 'src-ssr/controller/common/singleOperate';
import * as mdUtils from 'src-ssr/controller/common/masterDetailOperate';

import listConfig from 'app/search/tenant/sale/list.js';
import summaryConfig from 'app/search/tenant/sale/summary.js';

const menuName = 'sale';
const modelName = 't_sales';
const modelLabel = '销售单';
const detailModelName = 't_sale_details'; // 从表名
const associatedFieldName = 'master_uid'; // 从表与主表的关联字段
const keyFieldName = 'uid';

// 取数据
async function fetchData ({ startRow, count, filter, sortBy, descending, user }) {
  const detailWhere = {};
  const where = await tools.assemblyWhere({
    filter,
    searchConfig: listConfig.search,
    fn: ({ where, field, search }) => {
      if (field === 'consumer_name') {
        where.uid = {
          [Op.in]: Sequelize.literal(`(
            SELECT s.uid
            FROM t_sales s
            LEFT JOIN m_consumers c ON s.consumer_uid=c.uid
            WHERE s.tenant_id = ${mysql.escape(user.tid)}
            AND c.nickname LIKE ${mysql.escape('%' + search.trim().replace(/%/g, '') + '%')}
          )`)
        };
      } else if (field === 'material_uid') {
        detailWhere[field] = tools.uuidToBin(search);
      } else if (field === 'status') {
        let subWhere = '';
        if (search === 'not') {
          subWhere = 'AND ISNULL((SELECT SUM(deliver_amount) FROM t_delivery_details d WHERE d.sale_detail_uid=s.uid))'
        } else if (search === 'part') {
          subWhere = 'AND s.amount > (SELECT SUM(deliver_amount) FROM t_delivery_details d WHERE d.sale_detail_uid=s.uid)';
        } else {
          subWhere = 'AND s.amount <= (SELECT SUM(deliver_amount) FROM t_delivery_details d WHERE d.sale_detail_uid=s.uid)';
        }
        where.uid = {
          [Op.in]: Sequelize.literal(`(
              SELECT s.master_uid
              FROM t_sale_details s
              WHERE s.tenant_id = ${mysql.escape(user.tid)}
              ${subWhere}
            )`)
        };
      }
    }
  });
  where.tenant_id = user.tid; // 只取该租户的数据

  const exclude = ['uid', 'history'];
  // 只取单条数据
  if (filter && filter.id) {
    where.id = filter.id;
    exclude.splice(exclude.indexOf('history'), 1);
  }

  const order = sortBy ? [[sortBy, descending ? 'DESC' : 'ASC']] : [];

  let right = { data: true };
  if (user) {
    right = await tools.checkUserRight(user, menuName, ['data']);
    if (!right.data) { // 是否有查看全部的权限
      where.create_user_id = user.id;
    }
  }

  let ret = { rows: [], count: 0 };
  try {
    ret = await models[modelName].findAndCountAll({
      attributes: {
        exclude
      },
      include: [{
        model: models.t_customers,
        attributes: [['nickname', 'customer_name']]
      }, {
        model: models.m_consumers,
        attributes: [['nickname', 'consumer_name']]
      }, {
        model: models[detailModelName],
        attributes: ['id', [Sequelize.literal('bin_to_uuid(t_sale_details.uid, 1)'), 'duid'], [Sequelize.literal('bin_to_uuid(t_sale_details.material_uid, 1)'), 'material_uid'], 'amount', 'price', 'tax_rate', 'money', 'remark'],
        include: [{
          model: models.t_materials,
          attributes: ['material_name', 'material_model', 'material_name_model', 'material_unit', ['default_image', 'material_image']]
        }, {
          model: models.t_delivery_details,
          attributes: ['deliver_amount']
        }],
        required: Object.keys(detailWhere).length > 0,
        where: detailWhere
      }],
      where,
      order,
      offset: count ? startRow : undefined,
      limit: count || undefined,
      group: count ? `${modelName}.id` : undefined
    });
  } catch (e) {
    console.error(modelLabel + '列表出错', e);
  }

  // 处理其它数据
  const rows = [];
  for (const row of ret.rows) {
    const jsonRow = row.toJSON();
    jsonRow.customer_uid = tools.binToUuid(jsonRow.customer_uid);

    tools.flatDetail(['t_customer', 'm_consumer'], jsonRow); // 拉平信息

    if (jsonRow[detailModelName]) {
      // 把明细里的信息拉平
      for (const detail of jsonRow[detailModelName]) {
        tools.flatDetail(['t_material'], detail);

        // 统计发货状态
        const deliveryAmount = detail.t_delivery_details.reduce((a, b) => a + b.deliver_amount, 0);
        detail.status = 'not';
        if (deliveryAmount > 0 && deliveryAmount < detail.amount) {
          detail.status = 'part';
        } else if (deliveryAmount >= detail.amount) {
          detail.status = 'done';
        }
        delete detail.t_delivery_details;

        // 数量转数值
        for (const key of ['amount', 'price', 'tax_rate', 'money']) {
          if (detail[key]) detail[key] = Number(detail[key]);
        }
      }
    }
    // 数量转数值
    for (const key of ['total_money']) {
      if (jsonRow[key]) jsonRow[key] = Number(jsonRow[key]);
    }

    if (user) jsonRow._owner = row.create_user_id == user.id; // 是否为拥有者
    rows.push(jsonRow);
  }

  if (filter && filter.id) {
    if (rows.length > 0) {
      return rows[0];
    } else {
      return {};
    }
  } else {
    return {
      data: rows,
      rowsNumber: typeof ret.count === 'object' ? ret.count.length : ret.count
    };
  }
}

// 列表
export const list = async (req, res) => {
  const { startRow, count, filter, sortBy, descending } = req.body;
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  const ret = await fetchData({ startRow, count, filter, sortBy, descending, user });
  res.json(ret);
};

// 详细信息
export const detail = async (req, res) => {
  const { id } = req.body;

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const ret = await fetchData({ filter: { id }, user });

  res.json(ret);
};

/**
 * 新增
 */
export const create = async (req, res) => {
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const { formData } = req.body;
  // 生成编号
  if (!formData.bill_no) {
    formData.bill_no = await autocode.newCode({ tid: user.tid, code: 'SL' }); // 生成编号
  }
  formData.type = 'enter'; // 默认值
  // 转换customer_uid
  if (formData.customer_uid) {
    formData.customer_uid = tools.uuidToBin(formData.customer_uid);
  }

  // 转换material_uid
  if (formData[detailModelName] && formData[detailModelName].length > 0) {
    // 明细转json
    if (typeof formData[detailModelName] === 'string') {
      formData[detailModelName] = JSON.parse(formData[detailModelName]);
    }
  }
  formData[detailModelName].forEach(row => {
    row.material_uid = tools.uuidToBin(row.material_uid);
  });

  const ret = await mdUtils.createData({ modelName, modelLabel, menuName, detailModelName, associatedFieldName, keyFieldName }, req, true);

  workflow.broadcast('new_order', `${moment().format('YY/MM/DD HH:mm')} 新订单 ${formData.bill_no} 到达，有 ${formData[detailModelName].length} 笔货物需要配送`, user.tid);

  res.json(ret);
};

/**
 * 更新
 */
export const update = async (req, res) => {
  const { id, formData } = req.body;
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);

  // 转换customer_uid
  if (formData.customer_uid) {
    formData.customer_uid = tools.uuidToBin(formData.customer_uid);
  }

  if (formData[detailModelName] && formData[detailModelName].length > 0) {
    // 明细转json
    if (typeof formData[detailModelName] === 'string') {
      formData[detailModelName] = JSON.parse(formData[detailModelName]);
    }
  }

  // 被删除明细的前置检查
  const [rows] = await mysql.query(`SELECT d.id
    FROM t_sale_details d
    LEFT JOIN t_sales m ON d.master_uid=m.uid
    WHERE d.tenant_id=? AND m.id=? AND id NOT IN (?)`, [user.tid, id, formData[detailModelName].map(v => v.id)]);
  if (rows.length > 0) {
    const [dRows] = await mysql.query(`SELECT m.bill_no, mt.material_name_model
      FROM t_delivery_details d
      LEFT JOIN t_deliveries m ON d.master_uid=m.uid
      LEFT JOIN t_sale_details sd ON d.sale_detail_uid=sd.uid
      LEFT JOIN t_sales sm ON sd.master_uid=sm.uid
      LEFT JOIN t_materials mt ON sd.material_uid=mt.uid
      WHERE d.tenant_id=? AND sm.id=? AND sd.id IN (?)`, [user.tid, id, rows.map(v => v.id)]);
    if (dRows.length > 0) {
      return res.json({ result: 'fail', message: dRows.map(v => `${v.material_name_model}已配送(配送单号:${v.bill_no})，不能删除`).join('<br>') });
    }
  }

  formData[detailModelName].forEach(row => {
    row.material_uid = tools.uuidToBin(row.material_uid);
  });

  const ret = await mdUtils.updateData({ modelName, modelLabel, menuName, detailModelName, associatedFieldName, keyFieldName }, req, true);

  res.json(ret);
};

/**
 * 删除
 */
export const destroy = async (req, res) => {
  const { id } = req.body;
  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  // 删除前置检查
  const checkModels = [
    async (id) => {
      const [rows] = await mysql.query(`SELECT m.bill_no, mt.material_name_model
        FROM t_delivery_details d
        LEFT JOIN t_deliveries m ON d.master_uid=m.uid
        LEFT JOIN t_sale_details sd ON d.sale_detail_uid=sd.uid
        LEFT JOIN t_sales sm ON sd.master_uid=sm.uid
        LEFT JOIN t_materials mt ON sd.material_uid=mt.uid
        WHERE d.tenant_id=? AND sm.id=?`, [user.tid, id]);
      if (rows.length > 0) {
        return rows.map(v => `${v.material_name_model}已配送(配送单号:${v.bill_no})，不能删除`);
      } else {
        return null;
      }
    }
  ];

  const checkResult = [];
  for (const fn of checkModels) {
    const msg = await fn(req.body.id);
    msg && checkResult.push(...msg);
  }
  if (checkResult.length > 0) {
    return res.json({ result: 'fail', message: checkResult.join('<br>') });
  }

  // const snapshot = await fetchData({ filter: { id: req.body.id }, user });
  const ret = await mdUtils.destroyData({ modelName, modelLabel, menuName, detailModelName, associatedFieldName, keyFieldName }, req, true);

  res.json(ret);
};

// 统计
export const summary = async (req, res) => {
  const { startRow, count, filter, sortBy, descending, columns } = req.body;

  const where = await tools.assemblyWhere({
    filter,
    searchConfig: summaryConfig.search,
    raw: true,
    fn: ({ where, field, search }) => {
      if (field === 'status') {
        let subWhere = '';
        if (search === 'not') {
          subWhere = 'ISNULL((SELECT SUM(deliver_amount) FROM t_delivery_details v WHERE v.sale_detail_uid=d.uid))'
        } else if (search === 'part') {
          subWhere = 'd.amount > (SELECT SUM(deliver_amount) FROM t_delivery_details v WHERE v.sale_detail_uid=d.uid)';
        } else {
          subWhere = 'd.amount <= (SELECT SUM(deliver_amount) FROM t_delivery_details v WHERE v.sale_detail_uid=d.uid)';
        }
        where.push(subWhere);
      }
    }
  });

  const user = await tools.getLoginUser(req.get('Authorization').split(' ')[1]);
  const right = await tools.checkUserRight(user, menuName, ['data']);
  if (!right.data) {
    // 是否有查看全部的权限
    where.push(`m.create_user_id=${user.id}`);
  }

  const order = [];
  if (['year', 'month', 'day'].includes(sortBy)) {
    // 日期型的一起排序
    if (columns.includes('year')) {
      order.push(`year ${descending ? 'DESC' : 'ASC'}`);
    }
    if (columns.includes('month')) {
      order.push(`month ${descending ? 'DESC' : 'ASC'}`);
    }
    if (columns.includes('day')) {
      order.push(`day ${descending ? 'DESC' : 'ASC'}`);
    }
  } else {
    sortBy && columns.includes(sortBy) && order.push(`${sortBy} ${descending ? 'DESC' : 'ASC'}`);
  }

  const limit = count ? `LIMIT ${startRow}, ${count}` : '';

  // group by 字段
  const groupField = columns.filter((v) => ['year', 'month', 'day', 'create_user_name',
    'type', 'customer_name', 'consumer_name', 'material_name_model', 'material_unit'].includes(v)).join(', ');

  const allDataSql = `SELECT @idx:=@idx+1 AS id, ${groupField ? groupField + ',' : ''}
    COUNT(*) bill_count, SUM(amount) amount, SUM(money) money, SUM(deliver_amount) deliver_amount
    FROM (
      SELECT FROM_UNIXTIME(m.create_time, '%Y') year,
      FROM_UNIXTIME(m.create_time, '%m') month, FROM_UNIXTIME(m.create_time, '%d') day,
      m.create_user_name, m.type, c.nickname customer_name, n.nickname consumer_name,
      t.material_name_model, t.material_unit, d.amount, d.money,
      IFNULL((SELECT SUM(tdd.deliver_amount) FROM t_delivery_details tdd WHERE tdd.sale_detail_uid=d.uid), 0) deliver_amount
      FROM t_sales m
      LEFT JOIN t_sale_details d ON m.uid=d.master_uid
      LEFT JOIN t_materials t ON d.material_uid=t.uid
      LEFT JOIN t_customers c ON m.customer_uid=c.uid
      LEFT JOIN m_consumers n ON m.consumer_uid=n.uid
      ${where.length > 0 ? ' WHERE ' + where.join(' AND ') : ''}
    ) a
    , (SELECT @idx:=0) p
    GROUP BY ${groupField || ''}`;

  const [stat] = await mysql.query(`SELECT COUNT(*) cnt,
    IFNULL(SUM(bill_count), 0) bill_count,
    IFNULL(SUM(amount), 0) amount,
    IFNULL(SUM(money), 0) money,
    IFNULL(SUM(deliver_amount), 0) deliver_amount
    FROM (${allDataSql}) a`);

  let rows = [];
  const sql = `${allDataSql}
    ${order.length > 0 ? ' ORDER BY ' + order.join(',') : ''}
    ${limit}`;
  try {
    [rows] = await mysql.query(sql);
  } catch (e) {
    console.log(e, sql);
  }

  let bill_count = 0, amount = 0, money = 0, deliver_amount = 0;
  rows.forEach((row) => {
    // 数量转数值
    for (const key of ['bill_count', 'amount', 'money', 'deliver_amount']) {
      if (row[key]) row[key] = Number(row[key]);
    }

    bill_count += row.bill_count;
    amount += row.amount;
    money += row.money;
    deliver_amount += row.deliver_amount;
  });

  res.json({
    data: rows,
    rowsNumber: stat[0].cnt,
    summary: {
      total: {
        bill_count: Number(stat[0].bill_count),
        amount: Number(stat[0].amount),
        money: Number(stat[0].money),
        deliver_amount: Number(stat[0].deliver_amount)
      },
      page: {
        bill_count,
        amount,
        money,
        deliver_amount
      }
    }
  });
};
